// 数据库连接工具
const mysql = require('mysql2/promise');
const config = require('../config/database');

// 创建连接池
const pool = mysql.createPool({
  host: config.mysql.host,
  user: config.mysql.user,
  password: config.mysql.password,
  database: config.mysql.database,
  port: config.mysql.port,
  waitForConnections: true,
  connectionLimit: config.mysql.pool.max,
  queueLimit: 0,
  // 启用Promise API
  Promise: Promise
});

// 添加表前缀函数
function addTablePrefix(tableName) {
  const prefix = config.mysql.prefix || '';
  // 如果表名已经包含前缀，则不再添加
  if (tableName.startsWith(prefix)) {
    return tableName;
  }
  return `${prefix}${tableName}`;
}

// 兼容旧版函数名
function getTableName(tableName) {
  return addTablePrefix(tableName);
}

// 执行SQL查询
async function query(sql, params = []) {
  let connection;
  try {
    // 从连接池获取连接
    connection = await pool.getConnection();
    
    if (config.debug) {
      console.log('Executing SQL:', sql);
      console.log('Parameters:', params);
    }
    
    // 执行查询
    const [rows] = await connection.execute(sql, params);
    return rows;
  } catch (error) {
    console.error('Database query error:', error);
    throw error;
  } finally {
    // 释放连接回连接池
    if (connection) {
      connection.release();
    }
  }
}

// 执行事务
async function transaction(callback) {
  let connection;
  try {
    // 从连接池获取连接
    connection = await pool.getConnection();
    
    // 开始事务
    await connection.beginTransaction();
    
    // 执行回调，传入连接供事务内使用
    const result = await callback({
      query: (sql, params = []) => connection.execute(sql, params),
      getTableName,
      addTablePrefix
    });
    
    // 提交事务
    await connection.commit();
    return result;
  } catch (error) {
    // 发生错误，回滚事务
    if (connection) {
      await connection.rollback();
    }
    console.error('Transaction error:', error);
    throw error;
  } finally {
    // 释放连接回连接池
    if (connection) {
      connection.release();
    }
  }
}

// 检查数据库连接
async function checkConnection() {
  try {
    const connection = await pool.getConnection();
    connection.release();
    return { success: true, message: 'Database connection successful' };
  } catch (error) {
    console.error('Database connection failed:', error);
    // 返回更友好的错误消息
    let friendlyMessage = 'Database connection failed';
    if (error.code === 'ECONNREFUSED') {
      friendlyMessage = `Failed to connect to database server at ${config.mysql.host}:${config.mysql.port || 3306}`;
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      friendlyMessage = 'Access denied: Invalid username or password';
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      friendlyMessage = `Database '${config.mysql.database}' does not exist`;
    }
    
    return { 
      success: false, 
      message: friendlyMessage,
      error: error.message,
      code: error.code 
    };
  }
}

// 关闭连接池
async function closePool() {
  await pool.end();
}

module.exports = {
  query,
  transaction,
  getTableName,
  addTablePrefix,
  checkConnection,
  closePool,
  pool
};